﻿/*********************物流项目三组************************
*  功能描述：员工实现接口
*  作者：张笑宇
*  时间：2022-01-10
*  修改人：张笑宇
*  修改时间：2022-01-10
*********************物流项目三组************************/
using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TodoApp.EntityFrameworkCore;
using TodoApp.ResultModel;
using TodoApp.TMS_SetUp.Departments;
using TodoApp.TMS_SetUp.Positions;
using TodoApp.TMS_SetUp.Roles;
using Volo.Abp.DependencyInjection;
using Volo.Abp.Domain.Repositories.Dapper;
using Volo.Abp.EntityFrameworkCore;

namespace TodoApp.TMS_personnelmatters.Employeess
{
    public class TMS_EmployeesRepository : DapperRepository<TodoAppDbContext>, ITransientDependency, ITMS_EmployeesRepository
    {
        public TMS_EmployeesRepository(IDbContextProvider<TodoAppDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }
        /// <summary>
        /// 分页显示
        /// </summary>
        /// <param name="name"></param>
        /// <param name="department"></param>
        /// <param name="position"></param>
        /// <param name="phone"></param>
        /// <param name="category"></param>
        /// <param name="pageindex"></param>
        /// <param name="pagesize"></param>
        /// <returns></returns>
        public async Task<PageResponse<TMS_EmployeesDto>> GetEmployees(string name,string department,string position,string phone,string category,int pageindex,int pagesize)
        {
            PageResponse<TMS_EmployeesDto> response = new PageResponse<TMS_EmployeesDto>();
            var dbConnection = await GetDbConnectionAsync();
            var sql = "select a.*,b.Department_Name,c.Position_Name,d.Dictionary_Name,e.Dictionary_Name as Dictionary_Names,f.Dictionary_Name as Dictionary_Namess from TMS_Employees a join TMS_Department b on a.Department_Id = b.Id join TMS_Position c on a.Position_Id = c.Id join TMS_Dictionary d on a.Employees_EducationBackground = d.Id join TMS_Dictionary e on a.Employees_Marriage = e.Id join TMS_Dictionary f on a.Employees_Category = f.Id where Employees_Delete = 'true'";
            var list = await dbConnection.QueryAsync<TMS_EmployeesDto>(sql, transaction: await GetDbTransactionAsync());
            response.PageTotal = list.Count();
            if (name != null)
            {
                sql += $" and a.Employees_Name like '%{name}%'";
            }
            if (department != null)
            {
                sql += $" and a.Department_Id = '{department}'";
            }
            if (position != null)
            {
                sql += $" and a.Position_Id = '{position}'";
            }
            if (phone != null)
            {
                sql += $" and a.Employees_Phone = '{phone}'";
            }
            if (category != null)
            {
                sql += $" and a.Employees_Category = '{category}'";
            }
            sql += $" order by a.Employees_DateTime offset {(pageindex-1)*pagesize} rows fetch next {pagesize} rows only";
            var lists = await dbConnection.QueryAsync<TMS_EmployeesDto>(sql, transaction: await GetDbTransactionAsync());
            response.PageData = lists.ToList();
            return response;
        }
        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="tMS_Employees"></param>
        /// <returns></returns>
        public async Task<int> CreateEmployees(TMS_Employees tMS_Employees)
        {

            var dbConnection = await GetDbConnectionAsync();
            return await dbConnection.ExecuteAsync($"insert into TMS_Employees values(NEWID(),'{tMS_Employees.Employees_Name}','{tMS_Employees.EmployeesSex}','{tMS_Employees.Employees_Phone}','{tMS_Employees.Employees_Universities}','{tMS_Employees.Employees_Professional}','{tMS_Employees.Employees_Address}','{tMS_Employees.Employees_EducationBackground}','{tMS_Employees.Employees_PoliticalLandscape}','{tMS_Employees.Employees_National}','{tMS_Employees.Employees_NativePlace}','{tMS_Employees.Employees_Marriage}','{tMS_Employees.Employees_DateBirth}','{tMS_Employees.Employees_Email}','{tMS_Employees.Employees_IdCard}','{tMS_Employees.Department_Id}','{tMS_Employees.Position_Id}','{tMS_Employees.Employees_Category}','1','1',getdate())", null,await GetDbTransactionAsync());
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> DeleteEmployees(string id)
        {

            var dbConnection = await GetDbConnectionAsync();
            var count = 0;
            var ids = id.Split(',');
            foreach (var item in ids)
            {
                var sql = $"update TMS_Employees set Employees_Delete='false' where Id = '{item}'";
                count += await dbConnection.ExecuteAsync(sql, null,
                 await GetDbTransactionAsync());
            }
            return count;
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="tMS_Employees"></param>
        /// <returns></returns>
        public async Task<int> UpdateEmployees(TMS_EmployeesDto tMS_Employees)
        {
            var dbConnection = await GetDbConnectionAsync();
            return await dbConnection.ExecuteAsync($"update TMS_Employees set Employees_Name='{tMS_Employees.Employees_Name}',EmployeesSex='{tMS_Employees.EmployeesSex}',Employees_Phone='{tMS_Employees.Employees_Phone}',Employees_Universities='{tMS_Employees.Employees_Universities}',Employees_Professional='{tMS_Employees.Employees_Professional}',Employees_Address='{tMS_Employees.Employees_Address}',Employees_EducationBackground='{tMS_Employees.Employees_EducationBackground}',Employees_PoliticalLandscape='{tMS_Employees.Employees_PoliticalLandscape}',Employees_National='{tMS_Employees.Employees_National}',Employees_NativePlace='{tMS_Employees.Employees_NativePlace}',Employees_Marriage='{tMS_Employees.Employees_Marriage}',Employees_DateBirth='{tMS_Employees.Employees_DateBirth}',Employees_Email='{tMS_Employees.Employees_Email}',Employees_IdCard='{tMS_Employees.Employees_IdCard}',Department_Id='{tMS_Employees.Department_Id}',Position_Id='{tMS_Employees.Position_Id}',Employees_Category='{tMS_Employees.Employees_Category}' where Id='{tMS_Employees.Id}'", null,
                 await GetDbTransactionAsync());
        }
        /// <summary>
        /// 学历
        /// </summary>
        /// <returns></returns>
        public async Task<List<TMS_Dictionary>> GetEducationBackground()
        {
            var dbConnection = await GetDbConnectionAsync();
            return (await dbConnection.QueryAsync<TMS_Dictionary>("select * from TMS_Dictionary where Dictionary_Category = 'formal '", transaction: await GetDbTransactionAsync())).ToList();
        }
        /// <summary>
        /// 婚姻
        /// </summary>
        /// <returns></returns>
        public async Task<List<TMS_Dictionary>> GetMarriage()
        {
            var dbConnection = await GetDbConnectionAsync();
            return (await dbConnection.QueryAsync<TMS_Dictionary>("select * from TMS_Dictionary where Dictionary_Category = 'marriage'", transaction: await GetDbTransactionAsync())).ToList();
        }
        /// <summary>
        /// 员工类别
        /// </summary>
        /// <returns></returns>
        public async Task<List<TMS_Dictionary>> GetCategory()
        {
            var dbConnection = await GetDbConnectionAsync();
            return (await dbConnection.QueryAsync<TMS_Dictionary>("select * from TMS_Dictionary where Dictionary_Category = 'category'", transaction: await GetDbTransactionAsync())).ToList();
        }
        /// <summary>
        /// 角色
        /// </summary>
        /// <returns></returns>
        public async Task<List<TMS_Role>> GetRole()
        {
            var dbConnection = await GetDbConnectionAsync();
            return (await dbConnection.QueryAsync<TMS_Role>("select * from TMS_Role", transaction: await GetDbTransactionAsync())).ToList();
        }
        /// <summary>
        /// 部门
        /// </summary>
        /// <returns></returns>
        public async Task<List<TMS_Department>> GetDepartment()
        {
            var dbConnection = await GetDbConnectionAsync();
            return (await dbConnection.QueryAsync<TMS_Department>("select * from TMS_Department", transaction: await GetDbTransactionAsync())).ToList();
        }
        /// <summary>
        /// 职位
        /// </summary>
        /// <returns></returns>
        public async Task<List<TMS_Position>> GetPosition()
        {
            var dbConnection = await GetDbConnectionAsync();
            return (await dbConnection.QueryAsync<TMS_Position>("select * from TMS_Position", transaction: await GetDbTransactionAsync())).ToList();
        }
    }
}
